from lib.tools import cxt_connetMyql
from conf.setting import MYSQL_INFO
import tablib
from lib.tools import cxt_en_de


dataSql="SELECT tui.user_real_name ,\
 tui.user_mobile ,\
 tuii.user_id_card_no ,\
 tuii.user_nation,\
 tuii.user_native_place ,\
 tuii.user_issuing_authority,\
 tupi.user_living_province,\
 tupi.user_living_city,\
 tupi.user_living_area,\
 tupi.user_living_address,\
 tupi.user_living_address_detail,\
 tupi.user_company_name,\
 tupi.user_company_province,\
 tupi.user_company_city,\
 tupi.user_company_area,\
 tupi.user_company_address,\
 tupi.user_company_address_detail,\
 tupi.user_work_company_mobile,\
 tupi.user_relation_name_first,\
 tupi.user_relation_mobile_first,\
 tupi.user_relation_name_second,\
 tupi.user_relation_mobile_second FROM tt_user_index AS tui ,tt_user_personal_info tupi ,tt_user_idcard_info tuii WHERE tui.user_id = tupi.user_id AND tui.user_id = tuii.user_id AND tuii.user_sex = 1 LIMIT 1000 , 1100;"

con = cxt_connetMyql(**MYSQL_INFO)
res = con.executeSelectSql(dataSql)
newRes = []
for i in list(res):
    i=list(i)
    if i[0]:
        i[0] =cxt_en_de(i[0],'de')
    if i[1]:
        i[1]=cxt_en_de(i[1],'de')
    if i[2]:
        i[2]=cxt_en_de(i[2],'de')
    if i[-4]:
        i[-4]=cxt_en_de(i[-4],'de')
    if i[-3]:
        i[-3]=cxt_en_de(i[-3],'de')
    if i[-2]:
        i[-2]=cxt_en_de(i[-2],'de')
    if i[-1]:
        i[-1]=cxt_en_de(i[-1],'de')

    print(i)
    newRes.append(i)


headers = ['user_real_name', 'user_mobile', 'user_id_card_no', 'user_nation', 'user_native_place',
           'user_issuing_authority', 'user_living_province', 'user_living_city', 'user_living_area',
           'user_living_address', 'user_living_address_detail', 'user_company_name', 'user_company_province',
           'user_company_city', 'user_company_area', 'user_company_address', 'user_company_address_detail',
           'user_work_company_mobile', 'user_relation_name_first', 'user_relation_mobile_first',
           'user_relation_name_second', 'user_relation_mobile_second']

tabData = tablib.Dataset(*newRes, headers=headers, title='list')
with open('list01.xlsx', 'wb') as f:
    f.write(tabData.xlsx)



